import pymysql

class GenericCrudTool:
    def __init__(self, host, user, password, db, charset='utf8mb4', cursorclass=pymysql.cursors.DictCursor):
        self.config = {
            'host': host,
            'user': user,
            'password': password,
            'db': db,
            'charset': charset,
            'cursorclass': cursorclass
        }

    def get_db_connection(self):
        return pymysql.connect(**self.config)

    def execute_query(self, query, params=None):
        connection = self.get_db_connection()
        result = None
        try:
            with connection.cursor() as cursor:
                cursor.execute(query, params)
                if query.strip().upper().startswith('SELECT'):
                    result = cursor.fetchall()
                connection.commit()
        finally:
            connection.close()
        return result

    def insert(self, table, data):
        columns = ', '.join(data.keys())
        placeholders = ', '.join(['%s'] * len(data))
        query = f"INSERT INTO {table} ({columns}) VALUES ({placeholders})"
        return self.execute_query(query, list(data.values()))

    def select(self, table, columns='*', where=None, params=None):
        query = f"SELECT {columns} FROM {table}"
        if where:
            query += f" WHERE {where}"
        return self.execute_query(query, params)

    def update(self, table, data, where, params=None):
        set_clause = ', '.join([f"{key}=%s" for key in data.keys()])
        query = f"UPDATE {table} SET {set_clause} WHERE {where}"
        params = list(data.values()) + (params or [])
        return self.execute_query(query, params)

    def delete(self, table, where, params=None):
        query = f"DELETE FROM {table} WHERE {where}"
        return self.execute_query(query, params)
    
    def select_count(self, table, where=None, params=None):
        query = f"SELECT COUNT(1) as mycount FROM {table}"
        if where:
            query += f" WHERE {where}"
        connection = self.get_db_connection()
        count = 0
        try:
            with connection.cursor() as cursor:
                cursor.execute(query, params)
                result = cursor.fetchone()
                if result:
                    count = result['mycount']  # 假设 COUNT(*) 查询总是返回一个元组，其中第一个元素是计数
                connection.commit()
        finally:
            connection.close()
        return count
    
    def batch_update(self, table, data_list, id_key='id'):
        if not data_list:
            return  # No data to update

        # Extract the keys (columns) from the first item in the list
        columns = [key for key in data_list[0].keys() if key != id_key]

        # Prepare the SET clause for the SQL query
        set_clause = ', '.join([f"{key} = %s" for key in columns])

        # Construct the SQL query
        query = f"UPDATE {table} SET {set_clause} WHERE {id_key} = %s"

        # Prepare the parameters for each update operation
        params_list = []
        for data in data_list:
            values = [data[column] for column in columns]
            values.append(data[id_key])  # Add the ID value at the end for the WHERE clause
            params_list.append(tuple(values))

        # Execute the batch update using execute_query
        for params in params_list:
            self.execute_query(query, params)